﻿using System.Collections.Generic;
using System.Data;
using Pku.BookStore.Model;
using System.Data.Common;

namespace Pku.BookStore.DAL
{
    /// <summary>
    /// 数据访问类
    /// </summary>
    public class PublisherDAL : BaseDAL
    {
        string commandText = string.Empty;

        /// <summary>
        /// 增加出版社
        /// </summary>
        /// <param name="entity"></param>
        public int Add(PublisherModel entity)
        {
            commandText = @"INSERT INTO Publisher(PublisherName)
VALUES(@PublisherName);
SET  @PublisherID=scope_identity();
";
            var command = DbMain.GetSqlStringCommand(commandText);
            DbMain.AddInParameter(command, "PublisherName", DbType.String, entity.PublisherName);
            DbMain.AddOutParameter(command, "PublisherID", DbType.Int32, entity.PublisherID);
            DbMain.ExecuteNonQuery(command);

            return (int)DbMain.GetParameterValue(command, "PublisherID");

        }

        /// <summary>
        /// 修改出版社信息
        /// </summary>
        /// <param name="entity"></param>
        public bool Modify(PublisherModel entity)
        {
            commandText = @"UPDATE Publisher SET PublisherName=@PublisherName WHERE PublisherID=@PublisherID";
            var command = DbMain.GetSqlStringCommand(commandText);
            DbMain.AddInParameter(command, "PublisherID", DbType.Int32, entity.PublisherID);
            DbMain.AddInParameter(command, "PublisherName", DbType.String, entity.PublisherName);

            return (int)DbMain.ExecuteNonQuery(command) == 1;
        }

        /// <summary>
        /// 查询
        /// </summary>
        /// <returns></returns>
        public List<PublisherModel> Search(PublisherParam param)
        {
            var list = new List<Model.PublisherModel>();
            DbCommand dbCommand = DbMain.GetStoredProcCommand("PublisherPageSearch");
            dbCommand.CommandTimeout = 600;
            DbMain.AddInParameter(dbCommand, "Page", DbType.Int32, param.PageNo);
            DbMain.AddInParameter(dbCommand, "Rows", DbType.Int32, param.PageRow);
            using (var dr = DbMain.ExecuteReader(dbCommand))
            {
                while (dr.Read())
                {
                    list.Add(ConvertReaderToEntity(dr));
                }
            }
            return list;
        }

        /// <summary>
        /// 获取实体
        /// </summary>
        /// <param name="publisherID"></param>
        /// <returns></returns>
        public PublisherModel GetEntityById(int publisherID)
        {
            commandText = @"SELECT PublisherID,PublisherName  FROM  Publisher WHERE PublisherID=@PublisherID";
            var command = DbMain.GetSqlStringCommand(commandText);
            DbMain.AddInParameter(command, "PublisherID", DbType.Int32, publisherID);
            using (var dr = DbMain.ExecuteReader(command))
            {
                if (dr.Read())
                {
                    return ConvertReaderToEntity(dr);
                }
            }
            return null;
        }

        /// <summary>
        /// 转换实体
        /// </summary>
        /// <param name="dr"></param>
        /// <returns></returns>
        private static PublisherModel ConvertReaderToEntity(IDataRecord dr)
        {
            return new PublisherModel
            {
                PublisherID = (int)dr["PublisherID"],
                PublisherName = dr["PublisherName"].ToString()
            };
        }

        /// <summary>
        /// 是否存在
        /// </summary>
        /// <param name="Str">关键字</param>
        /// <returns></returns>
        public bool Exist(string Str)
        {
            commandText = "SELECT COUNT(1) FROM Publisher p(NOLOCK) WHERE p.PublisherName=@PublisherName";
            var command = DbMain.GetSqlStringCommand(commandText.ToString());
            DbMain.AddInParameter(command, "PublisherName", DbType.String, Str);
            return (int)DbMain.ExecuteScalar(command) > 0;
        }

        /// <summary>
        /// 获取总数
        /// </summary>
        /// <param name="param"></param>
        /// <returns></returns>
        public int GetCount(Model.PublisherParam param)
        {
            string commandText = "SELECT COUNT(1) FROM Publisher o(NOLOCK) WHERE 1=1";
            var command = DbMain.GetSqlStringCommand(commandText);
            return (int)DbMain.ExecuteScalar(command);
        }

        /// <summary>
        /// 删除
        /// </summary>
        /// <param name="CategoryID"></param>
        /// <returns></returns>
        public bool Delete(int PublisherID)
        {
            commandText = "DELETE Publisher WHERE PublisherID=@PublisherID";
            var command = DbMain.GetSqlStringCommand(commandText.ToString());
            DbMain.AddInParameter(command, "PublisherID", DbType.Int32, PublisherID);
            return (int)DbMain.ExecuteNonQuery(command) > 0;
        }
    }

}